package com.efesco.stat.operation;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.efesco.common.DBTools;
import com.efesco.common.Tools;
import com.efesco.system.User;

/**
 * achievementRate_transactDatasetListener
 */
public class achievementRate_transactDatasetListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset)
     throws Exception {
	    String sql="",foreign_enterprise_id="";
	  	
	  	DoradoContext context = DoradoContext.getContext();
	    Object obj = context.getAttribute(context.SESSION, "User");
	    if (obj != null) {
	    	User user = (User) obj;
    		foreign_enterprise_id=user.getForeignEnterpriseId();
	    	String startDate = Tools.format(dataset.parameters().getDate("startDate"),"yyyy-MM-dd");
	    	String endDate = Tools.format(dataset.parameters().getDate("endDate"),"yyyy-MM-dd");
	    	String mainPart = dataset.parameters().getString("mainPart");
	    	if (dataset.parameters().getString("statItem").equals("1"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"st.create_date apply_date , hf.hire_operate_date transact_date " +
	    				"from s_employee_hire_fire hf, s_in_char_service_transaction st ,s_in_char_list il," +
	    				"s_employee_on_off_post eoop,s_employee emp,s_single_company cmp " +
	    				"where hf.in_char_service_transaction_id = st.in_char_service_transaction_id " +
	    				" and st.in_char_id=il.in_char_id and il.employee_on_off_id=eoop.employee_on_off_id " +
	    				" and eoop.employee_id=emp.employee_id and eoop.single_company_id=cmp.single_company_id " +
	    				" and st.status='2' and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and st.create_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and st.create_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("2"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"lc.input_date apply_date,lc.subscribe_date transact_date " +
	    				"from s_labor_contract lc,s_single_company cmp,s_employee emp,s_employee_on_off_post eoop " +
	    				"where lc.employee_id=emp.employee_id and lc.single_company_id=cmp.single_company_id " +
	    				"and lc.employee_on_off_id=eoop.employee_on_off_id and lc.status=2 and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and lc.input_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and lc.input_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("3"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"ann.apply_date apply_date,ann.transact_date " +
	    				"from s_annuity ann,s_employee_on_off_post eoop,s_employee emp,s_single_company cmp " +
	    				"where ann.employee_on_off_id=eoop.employee_on_off_id and eoop.employee_id=emp.employee_id " +
	    				"and eoop.single_company_id=cmp.single_company_id and ann.annuity_status>=3 and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and ann.apply_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and ann.apply_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("4"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"zm.apply_date apply_date, zm.input_date transact_date " +
	    				"from s_zhonghe_manage zm,s_employee_on_off_post eoop,s_employee emp,s_single_company cmp " +
	    				"where zm.employee_on_off_id=eoop.employee_on_off_id and eoop.employee_id=emp.employee_id " +
	    				"and eoop.single_company_id=cmp.single_company_id and zm.status>=4 and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and zm.apply_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and zm.apply_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("5"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"acc.apply_date apply_date,acc.transact_date " +
	    				"from s_accumulation acc,s_employee_on_off_post eoop,s_employee emp,s_single_company cmp " +
	    				"where acc.employee_on_off_id=eoop.employee_on_off_id and eoop.employee_id=emp.employee_id " +
	    				"and eoop.single_company_id=cmp.single_company_id and acc.accumulation_status>=3 and acc.accumulation_type=1 " +
	    				" and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and acc.apply_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and acc.apply_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	if (dataset.parameters().getString("statItem").equals("6"))  {
	    		sql="select distinct cmp.company_code,cmp.company_chinese_name,emp.employee_code,emp.employee_name,eoop.on_post_date," +
	    				"acc.apply_date apply_date,acc.transact_date " +
	    				"from s_accumulation acc,s_employee_on_off_post eoop,s_employee emp,s_single_company cmp " +
	    				"where acc.employee_on_off_id=eoop.employee_on_off_id and eoop.employee_id=emp.employee_id " +
	    				"and eoop.single_company_id=cmp.single_company_id and acc.accumulation_status>=3 and acc.accumulation_type=2 " +
	    				" and cmp.foreign_enterprise_id="+foreign_enterprise_id;
	    		if (!startDate.equals(""))  {
	    			sql = sql + " and acc.apply_date >= to_date('"+startDate+"','yyyy-mm-dd')";
	    		}
	    		if (!endDate.equals(""))  {
	    			sql = sql + " and acc.apply_date <= to_date('"+endDate+"','yyyy-mm-dd')";
	    		}
	    		if (!mainPart.equals(""))  {
	    			sql = sql + " and cmp.main_part = "+mainPart;
	    		}
	    	}
	    	
	    	if (!sql.equals(""))  {
	    		sql = sql + " order by cmp.company_code,emp.employee_code ";
	    	}
	    	SqlDataset sqlDataset=(SqlDataset)dataset;
	    	sqlDataset.setSql(sql);
	    }
    return true;
  }

}
